package com.ruoyi.service;

import cn.hutool.core.lang.Validator;
import cn.hutool.core.lang.tree.TreeNodeConfig;
import cn.hutool.core.lang.tree.TreeUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.druid.DbType;
import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.expr.SQLIdentifierExpr;
import com.alibaba.druid.sql.ast.expr.SQLPropertyExpr;
import com.alibaba.druid.sql.ast.statement.SQLSelect;
import com.alibaba.druid.sql.ast.statement.SQLSelectItem;
import com.alibaba.druid.sql.ast.statement.SQLSelectStatement;
import com.alibaba.druid.sql.dialect.oracle.ast.stmt.OracleSelectQueryBlock;
import com.alibaba.fastjson2.JSON;
import com.alibaba.fastjson2.JSONObject;
import com.baomidou.mybatisplus.core.toolkit.sql.SqlInjectionUtils;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.ruoyi.common.constant.BusinessConstant;
import com.ruoyi.common.core.domain.CustomData;
import com.ruoyi.common.core.domain.entity.SysDictData;
import com.ruoyi.common.core.domain.query.CustomDataConfigQuery;
import com.ruoyi.common.core.domain.vo.SelectOptionVO;
import com.ruoyi.common.core.mapper.CustomDataMapper;
import com.ruoyi.common.core.service.BaseServiceImpl;
import com.ruoyi.common.utils.SecurityUtils;
import com.ruoyi.common.utils.TreeUtils;
import com.ruoyi.mapper.SysDictDataMapper;
import com.ruoyi.mapper.SysDictTypeMapper;
import com.ruoyi.mapper.SysMenuMapper;
import com.ruoyi.vo.CustomDataResponseVO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.lang.reflect.Field;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;
import static com.alibaba.fastjson2.JSONWriter.Feature.PrettyFormat;
import static com.ruoyi.common.constant.BusinessConstant.CustomerDataType.BACK_SYSTEM;
import static com.ruoyi.common.utils.sql.SqlUtil.formatSql;
import static com.ruoyi.service.SysUserServiceImpl.addSystemParam;

/**
 * 自定义数据 接口实现类
 */
@Service
public class CustomDataServiceImpl extends BaseServiceImpl<CustomDataMapper, CustomData> {
    @Autowired
    private SysDictTypeMapper sysDictTypeMapper;
    @Autowired
    private SysDictDataMapper sysDictDataMapper;
    @Autowired
    private SysMenuMapper sysMenuMapper;
    @Autowired
    private CustomDataMapper customDataMapper;
    @Autowired
    private SysUserServiceImpl sysUserService;

    public CustomDataResponseVO tableGetList(Page pageExt, String menuId, Map<String, Object> param) {
        param.forEach((k, v) -> {
            SqlInjectionUtils.check((String) v);
        });
        CustomData action = baseMapper.getByOwnerId(BACK_SYSTEM, menuId);
        if (action == null) {
            action = new CustomData();
        }
        if (StrUtil.isBlank(action.getAttr())) {
            action.setAttr(JSON.toJSONString(new CustomDataConfigQuery.MetaInf(), PrettyFormat));
        }
        CustomDataConfigQuery.MetaInf meta = JSON.parseObject(action.getAttr(), CustomDataConfigQuery.MetaInf.class);
        CustomDataResponseVO customDataResponse = new CustomDataResponseVO();
        CustomDataConfigQuery customDataConfig = new CustomDataConfigQuery();
        if (meta == null) {
            meta = new CustomDataConfigQuery.MetaInf();
        }
        customDataConfig.setSql(action.getQuerySql());
        customDataConfig.setMeta(JSONObject.parseObject(action.getAttr()));
        customDataConfig.setId(menuId);

        if (StrUtil.isNotBlank(action.getQuerySql())) {
            addSystemParam(param);
            if (action.getQuerySql().contains("deptIds")) {
                param.put("deptIds", sysUserService.getCurrUserDeptAuthority().stream().collect(Collectors.joining(",")));
            }
            String sql = formatSql(action.getQuerySql(), param);
            if ("true".equals(meta.getIsTreeTable())) {
                List<LinkedHashMap> dataList = sysDictDataMapper.selectBySql(sql);
                List tree = TreeUtil.build(dataList, Integer.valueOf(meta.getTree().getRootId()), new TreeNodeConfig().setChildrenKey(meta.getTree().getChildrenKey())
                                .setIdKey(meta.getTree().getIdKey()).setParentIdKey(meta.getTree().getParentIdKey()).setNameKey(meta.getTree().getNameKey()),
                        (object, treeNode) -> {
                            treeNode.putAll(JSON.parseObject(JSON.toJSONString(object)));
                        });
                if (tree == null) {
                    tree = new ArrayList();
                }
                customDataResponse.setDataList(tree);
                customDataResponse.setTotal(tree.size());
            } else {
                try {
                    sysDictDataMapper.selectPage(pageExt, sql);
                } catch (Exception e) {

                }
                pageExt.getRecords().forEach(r -> {
                    LinkedHashMap<String, Object> row = (LinkedHashMap<String, Object>) r;
                    row.put("loginUserId", SecurityUtils.getUserId());
                });
                customDataResponse.setDataList(pageExt.getRecords());
                customDataResponse.setTotal(pageExt.getTotal());
            }
        }

        List<CustomDataConfigQuery.EditFormItemInf> columnList = meta.getColumns();
        List<CustomDataConfigQuery.EditFormItemInf> showColumnList = columnList.stream().filter(r -> r.isShow()).collect(Collectors.toList());
        if (showColumnList.size() > 9) {
            customDataResponse.setAllColumnList(showColumnList);
            customDataResponse.setTableColumnList(showColumnList.subList(0, 9));
            customDataResponse.setExpandColumnList(showColumnList);
        } else {
            customDataResponse.setAllColumnList(showColumnList);
            customDataResponse.setTableColumnList(showColumnList);
        }
        customDataResponse.setConfig(customDataConfig);
        return customDataResponse;
    }

    //从sql中匹配出查询条件，并去除内置的变量
    private List<CustomDataConfigQuery.EditFormItemInf> formatSqlToConditions(String sql) {
        List<CustomDataConfigQuery.EditFormItemInf> conditions = new ArrayList<>();
        // 定义匹配花括号中内容的正则表达式
        String regex = "\\{([^}]*)}";

        // 编译正则表达式
        Pattern pattern = Pattern.compile(regex);

        // 创建 Matcher 对象
        Matcher matcher = pattern.matcher(sql);

        // 查找匹配项
        while (matcher.find()) {
            // 获取匹配到的内容
            String match = matcher.group(1);

            // 进一步匹配 # 后的字段命名
            String fieldRegex = "#(\\w+)";
            Pattern fieldPattern = Pattern.compile(fieldRegex);
            Matcher fieldMatcher = fieldPattern.matcher(match);

            // 查找字段命名
            while (fieldMatcher.find()) {
                // 输出匹配到的字段命名
                String fieldName = fieldMatcher.group(1);
                conditions.add(new CustomDataConfigQuery.EditFormItemInf().setKey(fieldName));
            }
        }
        List<String> builtInVars = new ArrayList<String>() {
            {
                add("deptIds");
            }
        };
        conditions.removeIf(r -> builtInVars.contains(r.getKey()));
        conditions = conditions.stream().distinct().collect(Collectors.toList());
        return conditions;
    }

    public CustomDataConfigQuery parseSql(String sql) {
        CustomDataConfigQuery config = new CustomDataConfigQuery();
        List<CustomDataConfigQuery.EditFormItemInf> conditions = formatSqlToConditions(sql);
        List<CustomDataConfigQuery.EditFormItemInf> columnList = new ArrayList<>();
        ;
        try {
            analysisConditions(conditions);
            sql = formatSql(sql, new HashMap<>());

            SQLSelectStatement sqlStatement = (SQLSelectStatement) SQLUtils.parseSingleStatement(sql, DbType.oracle);
            SQLSelect sqlSelect = sqlStatement.getSelect();
            OracleSelectQueryBlock sqlSelectQueryBlock = (OracleSelectQueryBlock) sqlSelect.getQuery();
            List<SQLSelectItem> sqlSelectItemList = sqlSelectQueryBlock.getSelectList();

            for (SQLSelectItem selectItem : sqlSelectItemList) {
                CustomDataConfigQuery.EditFormItemInf formItem = new CustomDataConfigQuery.EditFormItemInf();
                if (selectItem.getAlias() != null) {
                    formItem.setProp(selectItem.getAlias());
                    formItem.setLabel(selectItem.getAlias());
                } else {
                    if (selectItem.getExpr() instanceof SQLPropertyExpr) {
                        formItem.setProp(((SQLPropertyExpr) selectItem.getExpr()).getName());
                    } else if (selectItem.getExpr() instanceof SQLIdentifierExpr) {
                        formItem.setProp(((SQLIdentifierExpr) selectItem.getExpr()).getName());
                    }
                    formItem.setLabel(formItem.getProp());
                }
                formItem.setType("text");
                if (Validator.hasChinese(formItem.getProp())) {
                    formItem.setShow(true);
                    columnList.add(formItem);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println(e.getMessage());
        }

        config.getMeta().put("columns", columnList);
        config.getMeta().put("conditions", conditions);
        return config;
    }

    public void analysisConditions(List<CustomDataConfigQuery.EditFormItemInf> conditions) throws NoSuchFieldException, IllegalAccessException {
        List<CustomDataConfigQuery> list = baseMapper.selectAllDynamic();
        List<CustomDataConfigQuery.EditFormItemInf> allConditions = new ArrayList<>();
        for (CustomDataConfigQuery customData : list) {
            if (StrUtil.isBlank(customData.getAttr())) {
                continue;
            }
            if (!customData.getMeta().containsKey("conditions")) {
                continue;
            }
            allConditions.addAll(JSON.parseArray(JSON.toJSONString(customData.getMeta().getJSONArray("conditions")), CustomDataConfigQuery.EditFormItemInf.class));
        }
        analysis(conditions, allConditions);
    }

    //对每一个属性检查，如果为null ， 则将历史出现次数最多的值赋予
    public void analysis(List<CustomDataConfigQuery.EditFormItemInf> conditions,
                         List<CustomDataConfigQuery.EditFormItemInf> allConditions) throws NoSuchFieldException, IllegalAccessException {
        for (CustomDataConfigQuery.EditFormItemInf condition : conditions) {
            // 使用反射遍历对象的属性
            Class<?> clazz = condition.getClass();
            Field[] fields = clazz.getDeclaredFields();

            for (Field field : fields) {
                // 设置为可访问，因为属性可能是私有的
                field.setAccessible(true);
                // 获取属性名和值
                String fieldName = field.getName();
                Object fieldValue = field.get(condition);
                if (fieldValue != null) {
                    continue;
                }
                field.set(condition, findMostFrequentValue(allConditions, fieldName));
                // 打印属性名和值
                System.out.println(fieldName + ": " + fieldValue);

            }
        }
    }

    //获取出现次数最高的值
    private Object findMostFrequentValue(List<CustomDataConfigQuery.EditFormItemInf> allConditions, String fieldName
    ) throws NoSuchFieldException, IllegalAccessException {
        Map<Object, Integer> frequencyMap = new HashMap();
        for (CustomDataConfigQuery.EditFormItemInf condition : allConditions) {
            Class<?> clazz = condition.getClass();
            Field field = clazz.getDeclaredField(fieldName);
            // 设置为可访问，因为属性可能是私有的
            field.setAccessible(true);
            Object fieldValue = field.get(condition);
            if (!frequencyMap.containsKey(fieldValue)) {
                frequencyMap.put(fieldValue, 0);
            }
            frequencyMap.put(fieldValue, frequencyMap.get(fieldValue) + 1);
        }
        Iterator it = frequencyMap.keySet().iterator();
        Object maxFrequencyKey = null;
        int maxFrequencyNum = 0;
        while (it.hasNext()) {
            Object key = it.next();
            int num = frequencyMap.get(key);
            if (num > maxFrequencyNum) {
                maxFrequencyKey = key;
            }
        }
        return maxFrequencyKey;
    }

    public List<SelectOptionVO> getDictType(String type) {
        //handleOldData();
        List<SelectOptionVO> list = new ArrayList();
        if (BusinessConstant.ElementType.SELECT.equals(type)) {
            list = sysDictTypeMapper.selectDictType();
            list.add(new SelectOptionVO("角色", "SQLID_ROLE_SELECT"));
            list.add(new SelectOptionVO("许可代码", "SQLID_LICENSE_CODE_SELECT"));

            List<CustomDataConfigQuery> configQueryList = customDataMapper.selectBy("字典类型");
            list.addAll(configQueryList.stream().map(r -> {
                return new SelectOptionVO(r.getTitle(), r.getDataName());
            }).collect(Collectors.toList()));


        } else if (BusinessConstant.ElementType.TREE_SELECT.equals(type)) {
            List<CustomDataConfigQuery> configQueryList = customDataMapper.selectBy("TREE下拉框组件");
            list.addAll(configQueryList.stream().map(r -> {
                return new SelectOptionVO(r.getDataName(), r.getDataName());
            }).collect(Collectors.toList()));
        } else if (BusinessConstant.ElementType.RADIO.equals(type)) {
            list = sysDictTypeMapper.selectRadioDictType();
        } else if (BusinessConstant.ElementType.SWITCH.equals(type)) {
            list.add(new SelectOptionVO("0是1否", "ISNOT01"));
        } else if (BusinessConstant.ElementType.CHECKBOX.equals(type)) {
            list = sysDictTypeMapper.selectDictType();
        }
        return list;
    }

    public Object getSelectOptionList(String dict_type) {
        List<SelectOptionVO> selectOptionList = null;
        CustomData customData = customDataMapper.getByDataName(dict_type);
        if (customData != null) {
            Map param = new HashMap<>();
            addSystemParam(param);
            String sql = formatSql(customData.getQuerySql(), param);
            List<LinkedHashMap> dataList = sysDictDataMapper.selectBySql(sql);
            if (customData.isTree()) {
                List tree = TreeUtil.build(dataList, TreeUtils.findRootId(dataList), new TreeNodeConfig(),
                        (object, treeNode) -> {
                            treeNode.putAll(JSON.parseObject(JSON.toJSONString(object)));
                        });
                if (tree == null) {
                    tree = new ArrayList();
                }
            } else {
                return dataList;
            }
        } else {
            List<SysDictData> list = sysDictDataMapper.selectDictDataByType(dict_type);
            selectOptionList = list.stream().map(r ->
                    new SelectOptionVO(r.getDictLabel(), r.getDictValue())
            ).collect(Collectors.toList());
        }
        //

        return selectOptionList;
    }

}
